/**
 * 
 */
package com.srini.util;

import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;

import com.srini.pojos.LoggedInUser;

/**
 * @author Sriniz
 * 
 */
public class TRMSUtil {
	private static TRMSUtil trmsUtil = null;

	String[] wheeler = { "Two Wheeler", "Four Wheeler", "Company Transport" };
	String[] weeks = { "", "Sunday", "Monday", "Tuesday", "Wednesday",
			"Thursday", "Friday", "Saturday" };

	private TRMSUtil() {

	}

	public static TRMSUtil getInstance() {
		if (trmsUtil == null) {
			trmsUtil = new TRMSUtil();
		}
		return trmsUtil;
	}

	public HSSFWorkbook generateReimbursementForm(LoggedInUser user,
			Calendar cal, Map days) throws IOException {
		String distance, address, workPlace;
		String vtype = user.getUserInfo().getVehicle();
		address = user.getUserInfo().getAddress();
		distance = user.getUserInfo().getDistanceFromHome();
		workPlace = user.getUserInfo().getWorkPlace();
		int costPerKm = (vtype.equals("0")) ? 3 : 6;
		if (vtype.equals("2")) {
			costPerKm = 0;
		}
		InputStream is = this.getClass().getResourceAsStream(
				"/Reimbursement_Details.xls");
		HSSFWorkbook wb = new HSSFWorkbook(is);
		HSSFSheet sheet = wb.getSheetAt(0);

		// Create a row and put some cells in it. Rows are 0 based.
		HSSFRow row = sheet.getRow(0);
		// Create a cell and put a value in it.
		HSSFCell cell = row.getCell(1);
		cell.setCellValue(user.getUserInfo().getFirstName() + " "
				+ user.getUserInfo().getLastName());
		row = sheet.getRow(1);
		cell = row.getCell(1);
		cell.setCellValue(user.getUserInfo().getEmpId());

		row = sheet.getRow(2);
		cell = row.getCell(1);
		cell.setCellValue("Enterprise - DU-5");

		row = sheet.getRow(3);
		cell = row.getCell(1);
		cell.setCellValue("Hyderabad");

		row = sheet.getRow(4);
		cell = row.getCell(1);
		cell.setCellValue(user.getUserInfo().getProject());
		row = sheet.getRow(5);
		cell = row.getCell(1);
		cell.setCellValue(user.getUserInfo().getManager());
		int rowCount = 8;

		int noOfDays = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
		HSSFCellStyle style = wb.createCellStyle();
		style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setBorderBottom((short) 1);
		style.setBorderRight((short) 1);

		int day = 0;

		for (int i = 0; i < noOfDays; i++) {
			row = sheet.getRow(rowCount);
			row.getCell(0).setCellValue(cal);
			day = checkDayOfWeek(days, cal);
			HSSFCellStyle clonedStyle = wb.createCellStyle();
			clonedStyle.cloneStyleFrom(style);
			switch (day) {
			case 1:
				String weekend = weeks[cal.get(Calendar.DAY_OF_WEEK)];
				mergeCells(sheet, row, style, weekend);
				break;
			case 2:
				clonedStyle
						.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
				mergeCells(sheet, row, clonedStyle, "Leave");
				break;
			case 3:
				clonedStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
				mergeCells(sheet, row, clonedStyle, "Holiday");
				break;
			default:
				row.getCell(1).setCellValue(address);
				row.getCell(2).setCellValue(workPlace);
				setNumericValue(row.getCell(3), Integer.valueOf(distance));
				row.getCell(4).setCellValue(wheeler[Integer.parseInt(vtype)]);
				row.getCell(5).setCellFormula(
						"PRODUCT(D" + (rowCount + 1) + "," + costPerKm + ")");
				setNumericValue(row.getCell(6),
						Integer.parseInt(user.getFoodAllowance()));
				setNumericValue(row.getCell(7),
						Integer.parseInt(user.getFoodAllowance()));

				break;
			}

			rowCount++;
			cal.add(Calendar.DAY_OF_MONTH, 1);
		}
		row = sheet.getRow(40);
		row.getCell(5).setCellFormula("SUM(F9:F39)");
		row.getCell(7).setCellFormula("SUM(H9:H39)");
		row = sheet.getRow(42);
		row.getCell(7).setCellFormula("SUM(F41, H41)");

		return wb;
	}

	public static int checkDayOfWeek(Map days, Calendar cal) {
		int day = 0, dayOfWeek;
		List leaves = (List) days.get("0");
		List holidays = (List) days.get("1");
		String date = Constants.DATE_FORMAT_MEDIUM.format(cal.getTime());
		dayOfWeek = cal.get(Calendar.DAY_OF_WEEK);
		if (dayOfWeek == Calendar.SATURDAY || dayOfWeek == Calendar.SUNDAY) {
			day = 1;
		} else if (leaves != null && leaves.contains(date)) {
			day = 2;
		} else if (holidays != null && holidays.contains(date)) {
			day = 3;
		}
		return day;
	}

	public HSSFWorkbook generateCashVoucher(LoggedInUser user, Calendar cal,
			Map days) throws IOException {
		InputStream is = this.getClass().getResourceAsStream(
				"/CashVoucher_TravelForm.xls");
		HSSFWorkbook wb = new HSSFWorkbook(is);
		HSSFSheet sheet = wb.getSheetAt(0);
		HSSFRow row = sheet.getRow(4);
		HSSFCell cell = row.getCell(1);
		cell.setCellValue(user.getUserInfo().getFirstName() + " "
				+ user.getUserInfo().getLastName());
		cell = row.getCell(8);
		cell.setCellValue("Enterprise - DU-5");

		row = sheet.getRow(6);
		cell = row.getCell(1);
		cell.setCellValue(user.getUserInfo().getEmpId());
		cell = row.getCell(8);
		cell.setCellValue(user.getUserInfo().getProject());

		row = sheet.getRow(8);
		cell = row.getCell(8);
		cell.setCellValue("WellsFargo");

		row = sheet.getRow(10);
		cell = row.getCell(1);
		cell.setCellValue(user.getUserInfo().getWorkPlace());

		row = sheet.getRow(15);
		cell = row.getCell(0);
		cell.setCellValue(cal);
		int dayCount = 0;
		int noOfDays = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
		List leaves = (List) days.get("0");
		List holidays = (List) days.get("1");
		DateFormat format = new SimpleDateFormat("MM/dd/yyyy");
		for (int i = 0; i < noOfDays; i++) {
			int dayOfWeek = cal.get(Calendar.DAY_OF_WEEK);
			String date = format.format(cal.getTime());

			if (dayOfWeek == Calendar.SATURDAY || dayOfWeek == Calendar.SUNDAY) {
			} else if (leaves != null && leaves.contains(date)) {
			} else if (holidays != null && holidays.contains(date)) {
			} else {
				dayCount++;
			}
			cal.add(Calendar.DAY_OF_MONTH, 1);
		}

		cal.add(Calendar.DAY_OF_MONTH, -1);
		row = sheet.getRow(17);
		cell = row.getCell(0);
		cell.setCellValue(cal);

		row = sheet.getRow(19);
		cell = row.getCell(0);
		cell.setCellValue("(" + dayCount + " days)");

		int totalCost = Integer.parseInt(user.getFoodAllowance()) * dayCount;

		row = sheet.getRow(16);
		cell = row.getCell(11);
		cell.setCellValue(totalCost);

		row = sheet.getRow(22);
		cell = row.getCell(2);
		cell.setCellValue(NumberToWords.getInWords(totalCost));

		row = sheet.getRow(22);
		cell = row.getCell(11);
		cell.setCellValue(totalCost);

		row = sheet.getRow(31);
		cell = row.getCell(1);
		cell.setCellValue(user.getUserInfo().getFirstName() + " "
				+ user.getUserInfo().getLastName());

		row = sheet.getRow(32);
		cell = row.getCell(1);
		cell.setCellValue(Calendar.getInstance());

		if (user.getUserInfo().getVehicle().equals("2")) {
			return wb;
		} else {
			return generateTravelForm(user, cal, days, wb, dayCount);
		}
	}

	public HSSFWorkbook generateTravelForm(LoggedInUser user, Calendar cal,
			Map days, HSSFWorkbook wb, int dayCount) throws IOException {

		HSSFSheet sheet = wb.getSheetAt(1);
		HSSFRow row = sheet.getRow(2);
		HSSFCell cell = row.getCell(1);
		cell.setCellValue(user.getUserInfo().getFirstName() + " "
				+ user.getUserInfo().getLastName());

		cell = row.getCell(6);
		cell.setCellValue("Enterprise - DU-5");
		cell = row.getCell(9);
		cell.setCellValue(user.getUserInfo().getProject());

		row = sheet.getRow(3);
		cell = row.getCell(1);
		cell.setCellValue(user.getUserInfo().getEmpId());

		cell = row.getCell(6);
		cell.setCellValue("Wellsfargo");
		cell = row.getCell(9);
		cell.setCellValue(user.getUserInfo().getWorkPlace());

		Calendar cal1 = Calendar.getInstance();
		cal1.set(Calendar.MONTH, cal.get(Calendar.MONTH));
		cal1.set(Calendar.DATE, 1);

		row = sheet.getRow(16);
		cell = row.getCell(0);
		cell.setCellValue(cal1);

		row = sheet.getRow(18);
		cell = row.getCell(0);
		cell.setCellValue(cal);

		row = sheet.getRow(19);
		cell = row.getCell(0);
		cell.setCellValue("(" + dayCount + " days)");

		String vtype = user.getUserInfo().getVehicle();

		row = sheet.getRow(17);
		cell = row.getCell(2);
		cell.setCellValue(wheeler[Integer.parseInt(vtype)]);

		cell = row.getCell(4);
		cell.setCellValue(user.getUserInfo().getAddress());

		cell = row.getCell(8);
		cell.setCellValue(user.getUserInfo().getWorkPlace());

		int costPerKm = (vtype.equals("0")) ? 3 : 6;
		int totalCostPerDay = Integer.parseInt(user.getUserInfo()
				.getDistanceFromHome()) * costPerKm;

		cell = row.getCell(11);
		setNumericValue(cell, (totalCostPerDay * dayCount));

		row = sheet.getRow(28);
		cell = row.getCell(11);
		setNumericValue(cell, (totalCostPerDay * dayCount));

		row = sheet.getRow(32);
		cell = row.getCell(1);
		cell.setCellValue(user.getUserInfo().getFirstName() + " "
				+ user.getUserInfo().getLastName());

		row = sheet.getRow(33);
		cell = row.getCell(1);
		cell.setCellValue(Calendar.getInstance());

		return wb;
	}

	private void mergeCells(HSSFSheet sheet, HSSFRow row, HSSFCellStyle style,
			String text) {
		CellRangeAddress add = new CellRangeAddress(row.getRowNum(),
				row.getRowNum(), 1, 7);
		sheet.addMergedRegion(add);

		row.getCell(1).setCellValue(text);
		row.getCell(1).setCellStyle(style);
		row.getCell(2).setCellStyle(style);
		row.getCell(3).setCellStyle(style);
	}

	private void setNumericValue(HSSFCell cell, double value) {
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
	}
}
